﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Extend.Models;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using System.Data;
using System.Text;
using static Estsh.Core.Model.EnumUtil.WmsEnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 盘点
    /// </summary>
    public class CycleCountRepository : BaseRepository<BaseEntity>, ICycleCountRepository
    {
        public CycleCountRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取盘点单据信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsCheckStock> GetCycleCountOrderList(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.order_no,c.enum_desc AS order_type_desc,a.create_userid, ");
                SqlStringBuilder.Append("a.create_time,d.emp_no,a.factory_code  FROM dbo.wms_check_stock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND	c.enum_type='wms_check_stock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id  ");
                SqlStringBuilder.Append("WHERE a.order_no like '%" + orderNo + "%' and a.order_status in ('20','30') and a.enabled='Y' ");
                List<WmsCheckStock> wmsChecks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return wmsChecks;
            }
        }

        /// <summary>
        /// 获取盘点单据信息通过订单号
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsCheckStock> GetCycleCountOrderListByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.order_no,c.enum_desc AS order_type_desc,b.carton_no,CONVERT(INT, b.qty) qty,CONVERT(INT, b.fact_qty) fact_qty,b.part_no ,a.create_userid,b.locate_id,b.locate_name, ");
                SqlStringBuilder.Append("a.create_time,b.zone_id,b.zone_name,d.emp_no,a.factory_code,p.part_spec   FROM dbo.wms_check_stock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND	c.enum_type='wms_check_stock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
                SqlStringBuilder.Append("WHERE a.order_no = '" + orderNo + "' and a.order_status in ('20','30') and a.enabled='Y' ");

                List<WmsCheckStock> wmsChecks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return wmsChecks;
            }
        }
        //根据库位名称获取库位信息
        public SysLocate GetLocateByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_locate (nolock) WHERE locate_name = '" + locateName + "'");
                SysLocate sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).FirstOrDefault();
                return sysLocates;
            }
        }

        //获取盘点范围
        public WmsCheckStockRange GetWmsCheckStockRangeByLocateName(string orderNo, string locateName, string zoneName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and  locate_name!='' and enabled='Y' ");
                WmsCheckStockRange wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
                if (wmsCheckStockLocate != null)
                {
                    stringBuilder = new StringBuilder();
                    stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and  locate_name='" + locateName + "' and enabled='Y' ");
                    wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
                }
                else
                {
                    stringBuilder = new StringBuilder();
                    stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and zone_name='" + zoneName + "' and enabled='Y' ");
                    wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
                }
                return wmsCheckStockLocate;
            }
        }

        /// <summary>
        /// 根据库位和条码获取周转箱中的 应盘和实盘数据
        /// </summary>
        /// <param name="orderNo"></param>
        /// <param name="locateName"></param>
        /// <returns></returns>
        public List<WmsCheckStock> GetWmsCheckStockQtyFactByTurnoverBoxLocateName(string orderNo, string locateName,string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("  SELECT  ISNULL(m.qty, 0) qty, ISNULL(n.fact_qty, 0) AS fact_qty,n.part_no,n.part_spec  FROM  ");
                SqlStringBuilder.Append("    (SELECT  CONVERT(INT, SUM(b.qty)) qty,b.part_no ,p.part_spec  FROM dbo.wms_check_stock a (nolock)  ");
                SqlStringBuilder.Append("                LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no   ");
                SqlStringBuilder.Append("                LEFT JOIN dbo.sys_part  p (nolock) ON b.part_no=p.part_no  ");
                SqlStringBuilder.Append("                WHERE  b.package_type=0 and a.order_no = '" + orderNo + "'  and b.locate_name='" + locateName + "' and carton_no='"+ cartonNo + "'  and a.order_status in ('20','30') and a.enabled='Y'  GROUP BY b.part_no ,p.part_spec) AS m ");
                SqlStringBuilder.Append("			 FULL JOIN  ");
                SqlStringBuilder.Append("	(SELECT  CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,p.part_spec  FROM dbo.wms_check_stock a (nolock)  ");
                SqlStringBuilder.Append("                LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no   ");
                SqlStringBuilder.Append("                LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no  ");
                SqlStringBuilder.Append("                WHERE b.package_type=0 and a.order_no = '" + orderNo + "' and b.dest_locate_name='" + locateName + "' and carton_no='"+ cartonNo + "'  and a.order_status in ('20','30') and a.enabled='Y'  GROUP BY b.part_no ,p.part_spec) AS n ");
                SqlStringBuilder.Append("				ON n.part_no = m.part_no ");
                List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }


        //根据库位获取应盘和实盘数据
        public List<WmsCheckStock_Extend> GetWmsCheckStockQtyFactByLocateName(string orderNo, string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("  SELECT  ISNULL(m.qty, 0) qty, ISNULL(n.fact_qty, 0) AS fact_qty,m.part_no,m.part_spec  FROM  ");
                SqlStringBuilder.Append("    (SELECT  CONVERT(INT, SUM(b.qty)) qty,b.part_no ,p.part_spec  FROM dbo.wms_check_stock a (nolock)  ");
                SqlStringBuilder.Append("                LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no   ");
                SqlStringBuilder.Append("                LEFT JOIN dbo.sys_part  p (nolock) ON b.part_no=p.part_no  ");
                SqlStringBuilder.Append("                WHERE b.package_type=0 and a.order_no = '" + orderNo + "'  and b.locate_name='" + locateName + "'  and a.order_status in ('20','30') and a.enabled='Y'  GROUP BY b.part_no ,p.part_spec) AS m ");
                SqlStringBuilder.Append("			 FULL JOIN  ");
                SqlStringBuilder.Append("	(SELECT  CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,p.part_spec  FROM dbo.wms_check_stock a (nolock)  ");
                SqlStringBuilder.Append("                LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no   ");
                SqlStringBuilder.Append("                LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no  ");
                SqlStringBuilder.Append("                WHERE b.package_type=0 and a.order_no = '" + orderNo + "' and b.dest_locate_name='" + locateName + "'  and a.order_status in ('20','30') and a.enabled='Y'  GROUP BY b.part_no ,p.part_spec) AS n ");
                SqlStringBuilder.Append("				ON n.part_no = m.part_no ");
                List<WmsCheckStock_Extend> checkStocks = dbConn.Query<WmsCheckStock_Extend>(SqlStringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }

        //获取完成盘点数据
        public List<WmsCheckStock> GetFinishCycleCountData(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("    SELECT  CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI  FROM dbo.wms_check_stock a (nolock) ");
                SqlStringBuilder.Append("                               LEFT JOIN	dbo.wms_check_stock_list b ON a.order_no=b.order_no    ");
                SqlStringBuilder.Append("                                LEFT JOIN	dbo.sap_cyco c ON c.IBLNR=b.order_no and c.matnr=b.part_no    ");
                SqlStringBuilder.Append("                               WHERE a.order_no = '" + orderNo + "'   ");
                SqlStringBuilder.Append("							   and a.order_status <> '40' and a.enabled='Y'  GROUP BY b.part_no,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI ");
                List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }

        //获取零件号信息
        public SysPart GetPartNOInfoByPartNo(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("    SELECT * from sys_part (nolock)  where part_no='" + partNo + "' ");
                SysPart sysParts = dbConn.Query<SysPart>(SqlStringBuilder.ToString()).FirstOrDefault();
                return sysParts;
            }
        }

        /// <summary>
        /// 根据周转箱条码获取箱条码
        /// </summary>
        /// <param name="cartonNo"></param>
        /// <returns></returns>
        public List<SysStock> GetRackPackageStockByCaronNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(" SELECT s.* FROM dbo.wms_rack_package (NOLOCK) AS r LEFT JOIN  dbo.sys_stock (NOLOCK) s ON r.corton_no=s.carton_no WHERE  r.enabled='Y' AND r.rack_no='" + cartonNo + "' ; ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        /// <summary>
        /// 根据周转箱条码获取盘点明细中的箱条码
        /// </summary>
        /// <param name="orderNo">盘点单号</param>
        /// <param name="turnoverBoxNo">周转箱条码</param>
        /// <param name="locateId">库位id</param>
        /// <returns></returns>
        public List<WmsCheckStockList_Extend> GetWmsCheckStockListkByBox(string orderNo, string turnoverBoxNo, string locateId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("  SELECT * FROM dbo.wms_check_stock_list(NOLOCK) ");
                stringBuilder.Append("  WHERE enabled='Y'  AND order_no='"+orderNo+"'  AND turnover_box_no='"+ turnoverBoxNo + "' ");
                stringBuilder.Append("  ORDER BY carton_no ASC ;  ");

                List<WmsCheckStockList_Extend> sysStocks = dbConn.Query<WmsCheckStockList_Extend>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        /// <summary>
        /// 根据周转箱条码获取盘点明细中的箱条码
        /// </summary>
        /// <returns></returns>
        public List<WmsRackPackage_Extend> GetWmsRackPackageInfo(string CartonNo, string FactoryId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("  SELECT * FROM dbo.wms_rack_package (nolock) WHERE enabled='Y' AND corton_no='" +CartonNo + "' AND factory_id='" +FactoryId + "' ; ");

                List<WmsRackPackage_Extend> sysStocks = dbConn.Query<WmsRackPackage_Extend>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }



        //根据条码获取库存条码
        public List<SysStock> GetStockByCaronNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE carton_no='" + cartonNo + "'");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }
        //判断盘点单范围库区是否存在零件号
        public List<WmsCheckStockRange> GetWmsCheckStockRangeByIsExsistPartNo(string orderNo, string locateName, string zoneName,string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT* FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and  (locate_name='" + locateName + "' or zone_name='" + zoneName + "') and part_no='" + partNo + "' and enabled='Y' ");
                List<WmsCheckStockRange> wmsCheckStocks = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).ToList();
                return wmsCheckStocks;
            }
        }
 
        //根据条码查询盘点单数据
        public List<WmsCheckStock> GetWmsCheckStockByCartonNo(string orderNo, string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT  a.order_no,c.enum_desc AS order_type_desc,CONVERT(INT, b.qty) qty,CONVERT(INT, b.fact_qty) fact_qty,b.part_no ,a.create_userid,b.locate_id,b.locate_name, ");
                stringBuilder.Append("a.create_time,b.zone_id,b.zone_name,d.emp_no,a.factory_code,a.order_status,p.part_spec,a.is_obvious  FROM dbo.wms_check_stock a (nolock) ");
                stringBuilder.Append("LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no  ");
                stringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND	c.enum_type='wms_check_stock_order_type'  ");
                stringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id  ");
                stringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
                stringBuilder.Append("WHERE  a.order_no ='" + orderNo + "' AND b.carton_no='" + cartonNo + "' and a.enabled='Y' ");
                List<WmsCheckStock> wmsCheckStocks = dbConn.Query<WmsCheckStock>(stringBuilder.ToString()).ToList();
                return wmsCheckStocks;
            }
        }

        //根据盘点单号获取盘点单
        public List<WmsCheckStock> GetWmsCheckStockByCartonNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT  * FROM dbo.wms_check_stock (nolock) ");
                stringBuilder.Append("WHERE order_no ='" + orderNo + "'");
                List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(stringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }
        //新增盘点单明细数据
        public int InsertCheckStockListInfo(WmsCheckStockList checkStockList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_list(order_no,warehouse_id,warehouse_name,zone_id,zone_name,locate_id,locate_name,dest_warehouse_id,dest_warehouse_name,dest_zone_id,dest_zone_name,dest_locate_id,dest_locate_name,part_id,part_no,part_spec,carton_no,qty,fact_qty,unit,sn_status,trans_status,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
                SqlStringBuilder.Append("     VALUES(@orderNo,@warehouseId,@warehouseName,@zoneId,@zoneName,@locateId,@locateName,@destwarehouseId,@destwarehouseName,@destzoneId,@destzoneName,@destlocateId,@destlocateName,@partId,@partNo,@partSpec,@cartonNo,@qty,@factQty,@unit,@snStatus,@transStatus, ");
                SqlStringBuilder.Append("		   @factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
                int recCheckStockListNum = dbConn.Execute(SqlStringBuilder.ToString(), checkStockList);
                return recCheckStockListNum;
            }
        }

        public int InsertCheckStockListInfos(List<WmsCheckStockList_Extend> checkStockList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();


                List<string> sqlStrings=new List<string>();
                for (int i = 0; i < checkStockList.Count(); i++)
                {
                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_list(order_no,warehouse_id,warehouse_name,zone_id,zone_name,locate_id,locate_name,dest_warehouse_id,dest_warehouse_name,dest_zone_id,dest_zone_name,dest_locate_id,dest_locate_name,part_id,part_no,part_spec,carton_no,qty,fact_qty,unit,sn_status,trans_status,factory_id,factory_code,enabled,create_userid,create_time,guid,package_type,turnover_box_no) ");
                    SqlStringBuilder.Append("     VALUES('" + checkStockList[i].OrderNo + "','" + checkStockList[i].WarehouseId + "','" + checkStockList[i].WarehouseName + "','" + checkStockList[i].ZoneId + "','" + checkStockList[i].ZoneName + "','" + checkStockList[i].LocateId + "','" + checkStockList[i].LocateName + "', ");
                    SqlStringBuilder.Append(" '" + checkStockList[i].DestWarehouseId + "','" + checkStockList[i].DestWarehouseName + "','" + checkStockList[i].DestZoneId + "', '" + checkStockList[i].DestZoneName + "','" + checkStockList[i].DestLocateId + "','" + checkStockList[i].DestLocateName + "','" + checkStockList[i].PartId + "','" + checkStockList[i].PartNo + "','" + checkStockList[i].PartSpec + "', ");
                    SqlStringBuilder.Append(" '" + checkStockList[i].CartonNo + "','" + checkStockList[i].Qty + "','" + checkStockList[i].FactQty + "', '" + checkStockList[i].Unit + "','" + checkStockList[i].SnStatus + "','" + checkStockList[i].TransStatus + "','" + checkStockList[i].FactoryId + "','" + checkStockList[i].FactoryCode + "','Y', ");
                    SqlStringBuilder.Append(" '" + checkStockList[i].CreateUserId + "',CONVERT(varchar(50), GETDATE(), 21),NEWID(),'"+ checkStockList[i].packageType + "','"+ checkStockList[i].turnoverBoxNo + "' );  ");

                    sqlStrings.Add(SqlStringBuilder.ToString());
                }
                int recCheckStockListNum = SQLTransaction(sqlStrings, null);

                return recCheckStockListNum;
            }
        }

        //修改盘点单状态
        public int UpdateCheckStockOrderStatus(string orderNo, string loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("UPDATE dbo.wms_check_stock SET order_status='40',update_userid=" + loginId + ",update_time=CONVERT(VARCHAR(20),GETDATE(),21) WHERE order_no='" + orderNo + "';");
                int refUpdateStatu = dbConn.Execute(stringBuilder.ToString());
                return refUpdateStatu;
            }
        }


        //事务批量执行添加、修改
        public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }
    }
}
